<?php
	session_start();
	include('conn.php');
	include('top.php');
?>

<div style="float: left;  position: relative;  left: 50%;">
	<div style="float: left;  position: relative;  left: -50%;">
	<h2>Upload File</h2>
	<br>
		<b>Mandatory fields:</b>
		
	<?php
		$qry1 = "select * from tran_field;";
			$qry = mysql_query($qry1);
			$data = array();			
			while($rows = mysql_fetch_array($qry))
			{		
				$data[] = array(					
					"inwarddate" => $rows['inwarddate'],					 
					"proposalno" => $rows['proposalno'],					 
					"policyno" => $rows['policyno'],					 
					"awbno" => $rows['awbno'],					 
					"filebarcode" => $rows['filebarcode'],					 
					"cartonbarcode" => $rows['cartonbarcode'],					 
					"districtid" => $rows['districtid'],					 
					"courierid" => $rows['courierid'],					 
					"remarks" => $rows['remarks'],					 					
				);
			}	
			foreach($data[0] as $k=>$v)
			{
				if($v=='y')
					echo $k.", ";
			}	
	?>
	<br/><br/>
	<form action="xltosql.php?fileadded=yes" method="post" enctype="multipart/form-data">
		Select xlsx file to upload:<br><br>
		<input type="file" name="fileToUpload" id="fileToUpload">
		<input type="submit" value="Upload File" name="submit">
	</form>


<?php
	if($_REQUEST['fileadded']=='yes'){
		$target_path = "uploads/";
		$target_path = $target_path . basename( $_FILES['fileToUpload']['name']);	
		if(move_uploaded_file($_FILES['fileToUpload']['tmp_name'], $target_path)) 
			echo "<h3>The file ".  basename( $_FILES['fileToUpload']['name'])." has been uploaded</h3>";	
		else
			echo "<h3>There was an error uploading the file, please try again!</h3>";	
		
		
		require 'PHPExcel_1.7.9_doc/Classes/PHPExcel.php';
		require_once 'PHPExcel_1.7.9_doc/Classes/PHPExcel/IOFactory.php';
		$path = "$target_path";

		$objPHPExcel = PHPExcel_IOFactory::load($path);
		foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
			$worksheetTitle     = $worksheet->getTitle();
			$highestRow         = $worksheet->getHighestRow(); // e.g. 10
			$highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
			$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
			$nrColumns = ord($highestColumn) - 64;
			echo "<br>The worksheet ".$worksheetTitle." has ";
			echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
			echo ' and ' . $highestRow . ' row.';
			echo '<br>Data: <table border="1">';
			for ($row = 1; $row <= $highestRow; ++ $row) {
				echo '<tr>';
				for ($col = 0; $col < $highestColumnIndex; ++ $col) {
					$cell = $worksheet->getCellByColumnAndRow($col, $row);
					
					if($row != 1){
					if (PHPExcel_Shared_Date::isDateTime($cell)){
						$val = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue());
						$val = ''.date('Y-m-d',$val);
					}
					else
						$val = $cell->getValue();
					}
					else
						$val = $cell->getValue();
					$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
					
					if($dataType == 'null')
						$val = '-';
					
					echo '<td style="border: 1px solid black;padding:6px;">' . $val . /*'<br>(Typ ' . $dataType . ')*/'</td>';
					
				}
				echo '</tr>';
			}
			echo '</table>';
		}
		
		foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
		$highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
		$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
		echo "<br>For worksheet '".$worksheet->getTitle()."':";
		for ($row = 2; $row <= $worksheet->getHighestRow(); ++ $row) {			
			$val=array();
			$ind = 0;
			for ($col = 0; $col < $highestColumnIndex; ++ $col) {
			   $cell = $worksheet->getCellByColumnAndRow($col, $row);
			   if (PHPExcel_Shared_Date::isDateTime($cell)){	   
					$val2 = PHPExcel_Shared_Date::ExcelToPHP($cell->getValue());
					$val[$ind] = ''.date('Y-m-d',$val2);
					$ind++;
				}
				else{
					$val[$ind] = $cell->getValue();
					$ind++;
				}
			}			
			if($val[7]==null || $val[8]==null)
				echo "<h4><span style='color:red;'>! You must enter the district(branch) and the courier name for row ".intval($row-1)."</span></h4>";
			else{
				$query1 = "select d.districtname,d.districtid, c.couriername,c.courierid from couriermast c,districtmast d";
				$chk1=false;
				$chk2=false;
				$res1 = mysql_query($query1);
				while($res2 = mysql_fetch_array($res1))
				{
					$dname = $res2['districtname'];
					$cname = $res2['couriername'];					
					if(strcasecmp($val[8], $dname)==0){
						$chk1=true;
						$dno = $res2['districtid'];						
					}
					if(strcasecmp($val[7],$cname)==0){
						$chk2=true;
						$cno = $res2['courierid'];
					}
				}
				
				if(!$chk1 || !$chk2)
					echo "<h4><span style='color:red;'>! You must enter a valid district name and/or courier name for row ".intval($row-1)."</span></h4>";
				else{ 
					$query2 = "select policyno from transactionmast";
					$chkpol = false;
					
					$res3 = mysql_query($query2);
					while($row2 = mysql_fetch_array($res3))
					{	
						$pno = $row2['policyno'];
						if($pno == $val[3])
							$chkpol = true;
					}//end all entires   
					if($chkpol)
						echo "<h4><span style='color:red;'>! This policy number: $val[3] already exists. Record cannot be inserted [for row ".intval($row-1)."]</span></h4>";
					else{
							$sql="INSERT INTO transactionmast(inwarddate,proposalno, policyno, filebarcode, cartonbarcode, awbno, courierid, districtid,enteredby, entrydt ) VALUES ('$val[1]','$val[2]','$val[3]','$val[4]','$val[5]','$val[6]','$cno','$dno','autosystem','".date("Y-m-d")."');";	
							echo "<h4>&radic; Data from row ".intval($row-1)." inserted successfully.</h4>";
							mysql_query($sql);	
							mysql_fetch_array($sql);
						}//end individual policy number
				}//end valid district,couriername  
			}//end mandatory district,couriername
		}
		}
	}
?>
</div></div>